package com.foreknow.demo15;

import com.foreknow.demo5.Emp;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {

    Connection conn;
    PreparedStatement pstmt;
    ResultSet rs;

    /**
     * 连接数据库的方法
     */
        public Connection getConnection() throws ClassNotFoundException, SQLException {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取链接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wjy?useUnicode=true&characterEncoding=utf8&useSSL=true","root","roat");
            return conn;
        }

    /**
     * 查询的方法
     * select * from Emp where username =? and password = ?
     */
        public ResultSet query(String sql,String username,String password) throws SQLException {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,username);
            pstmt.setString(2,password);
            rs = pstmt.executeQuery();//会将查询的结果保存到ResultSet集合里
            return rs;
        }

    /**
     * DML(insert delete update)
     * insert into Emp values(?,?)
     */
    public int DML(String sql) throws SQLException {
        Statement stmt = conn.createStatement();
        int isRight = stmt.executeUpdate(sql);
        return isRight;
    }

    /**
     * 关闭资源
     */
    public void close() throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    public static void main(String[] args) {
        DBUtil dbu = new DBUtil();
        try {
            //1. 连接数据库
            Connection conn = dbu.getConnection();
            //2. 做DML操作
            int isRight = dbu.DML("insert into Emp(username,password) values('tom','654321')");
            if (isRight == 1) {
                System.out.println("添加成功！");
            }
            //dbu.DML("insert into Emp(username,password) values('tom','654321')");
            //dbu.DML("insert into Emp(username,password) values('lily','567890')");
            //3. 查询数据表
            //ResultSet rs = dbu.query("select * from Emp");
//            List<Employee> list = new ArrayList<>();
//            while (rs.next()) {
//                //获取当前行的第几列数据
//                int id = rs.getInt("id");
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                //将结果集中的数据保存到对象中
//                Employee emp = new Employee();
//                emp.setId(id);
//                emp.setUsername(username);
//                emp.setPassword(password);
//                //将对象保存到集合中
//                list.add(emp);
//            }
//            for (Employee e:list) {
//                System.out.println(e.getUsername());
//            }
//            dbu.DML("delete from Emp where id = 1");
//            dbu.query("select * from Emp");
            //4. 关闭资源
            dbu.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}












